package com.navi.loaddata.loadService.impl.cellMappingData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.hisToGP.ProductHis;
import com.navi.loaddata.core.mesEntity.MesP1MESDB;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.core.util.ProcessUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Service
public class LoadCell2MappingInfoService {
    private static final Logger logger = LoggerFactory.getLogger(LoadCell2MappingInfoService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());

    public List<MesP1MESDB> loadModelInfo() throws Exception{
        List<MesP1MESDB> mesUser = new ArrayList<>();

        try {
            QueryRunner qr_mes_user = JdbcUtil.getQueryRunner_mes_user();
            String sql = "SELECT" +
                    " DISTINCT SUBSTR(productspecname,3,3) as glassModel," +
                    " CASE WHEN LENGTH(tftsubproductseq)=1 THEN '0'||tftsubproductseq ELSE tftsubproductseq END tftNo," +
                    " CASE WHEN LENGTH(cfsubproductseq)=1 THEN '0'||cfsubproductseq ELSE cfsubproductseq END cfNo" +
                    " FROM" +
                    " TPPOLICY A," +
                    " pospairsubproduct B" +
                    " WHERE" +
                    " 1 = 1" +
                    " AND factoryname = 'OC'" +
                    " AND b.CONDITIONID = A.CONDITIONID";
            mesUser = qr_mes_user.query(sql, new BeanListHandler<MesP1MESDB>(MesP1MESDB.class));
        }catch (Exception e){
            throw new ETLException2(ErrorCode.ERROR_GET_USER_INFO,e);
        }
        return mesUser;
    }

    public List<ProductHis> loadCell2Info(EtlConfD etlConfD) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());

        List<ProductHis> panelList = new ArrayList<>();

        try {
            QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();
            String sql = "select" +
                    " a.glass_id as panel_id," +
                    " SUBSTR(b.product_id_oc1,3,3) as prod_id," +
                    " b.tft_glass_id  as arrayGlassName," +
                    " case when b.cf_glass_id is null then '-1' else b.cf_glass_id end as cfGlassName," +
                    " a.ope_id as ope_id" +
                    " from wpp_csht_ope_his a" +
                    " left join wpp_sht_info b on a.glass_id=b.panel_id" +
                    " where 1=1" +
                    " and a.ope_id='L4100'" +
                    " and substring(glass_id,2,2)='18'" +
                    " and length(a.glass_id)=12" +
                    " and a.db_timestamp>='" + startTime + "'" +
                    " and a.db_timestamp<'" + endTime + "'";
            panelList = qr_gp.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
        }catch (Exception e){
            throw new ETLException2(ErrorCode.ERROR_GET_GP_CELL2_MAPPING,e);
        }
        for(ProductHis panel:panelList){
            if(StringUtils.isEmpty(panel.getProd_id())){
                logger.warn("glassId={} opeId={} prodId 信息为空，默认设置为495", panel.getPanel_id(),panel.getOpe_id());
                panel.setProd_id("495");
            }
            if(ProcessUtil.opeLimit(panel.getOpe_id())){
                logger.warn("glassId={} opeId={} opeId 信息不符合要求，跳过", panel.getPanel_id(),panel.getOpe_id());
                continue;
            }
        }
        return panelList;
    }


}
